/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modelo.helper;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import modelo.DTO.DTODataMining;
import modelo.DTO.DTORecursosAlumno;
import modelo.DTO.DTOVisitasAlumno;
import modelo.HibernateUtil;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @version 1.1
 * @author Ion B. Padilla
 */
public class RecursoHelper {
    private static RecursoHelper instance;

    public RecursoHelper() {
    }

    public static RecursoHelper getInstance() {
        if (null == instance) {
            instance = new RecursoHelper();
        }
        return instance;
    }
    /**
     * Metodo para conseguir loas recursos usados por alumnos
     */
    public List<DTODataMining> getRecursosDifPorAlumnos() {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username, count(distinct r.name) as numeroRecursos  FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u WHERE modu.name = 'resource'  AND cm.instance=r.id  AND modu.id=cm.module  AND cm.id=log.cmid  AND log.userid=u.id AND cmid!=0  GROUP BY log.userid ORDER BY u.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                lista.add(new DTORecursosAlumno((String) row[0], Long.toString((Long) row[1])));
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir loas recursos usados por Curso
     * @param curso el curso donde consultamos
     */
    public List<DTODataMining> getRecursosDifPorCursoAlumnos(int curso) {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username, count(distinct r.name) as numeroRecursos "
                    + " FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u "
                    + "WHERE modu.name = 'resource'  "
                    + "AND cm.instance=r.id  "
                    + "AND modu.id=cm.module  "
                    + "AND cm.id=log.cmid  "
                    + "AND log.userid=u.id "
                    + "AND log.course= "+curso
                    + "AND cmid!=0  "
                    + "GROUP BY log.userid "
                    + "ORDER BY u.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                lista.add(new DTORecursosAlumno((String) row[0], Long.toString((Long) row[1])));
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }

    /**
     * Metodo para conseguir los recursos utilizados por un alumno
     * @param nombre el nombre del alumno del que queremos saber los recursos utilizados
     */
    public DTODataMining getRecursosDifPorAlumno(String nombre) {
        DTORecursosAlumno dtoRecursos = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username, count(distinct r.name) as numeroRecursos  "
                    + "FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u "
                    + "WHERE modu.name = 'resource'  "
                    + "AND cm.instance=r.id  "
                    + "AND modu.id=cm.module  "
                    + "AND cm.id=log.cmid  "
                    + "AND log.userid=u.id "
                    + "AND cmid!=0 "
                    + "AND username='" + nombre + "' "
                    + "GROUP BY log.userid "
                    + "ORDER BY u.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoRecursos = new DTORecursosAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoRecursos;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir los recursos utilizados por un alumno
     * @param nombre el nombre del alumno del que queremos saber los recursos utilizados
     * @param curso curso del que queremos consultar
     */
    public DTODataMining getRecursosDifPorCursoAlumno(int curso,String nombre) {
        DTORecursosAlumno dtoRecursos = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT u.username, count(distinct r.name) as numeroRecursos  "
                    + "FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u "
                    + "WHERE modu.name = 'resource'  "
                    + "AND cm.instance=r.id  "
                    + "AND modu.id=cm.module  "
                    + "AND cm.id=log.cmid  "
                    + "AND log.userid=u.id "
                    + "AND cmid!=0 "
                    + "AND username='" + nombre + "' "
                    + "AND log.course= "+curso
                    + "GROUP BY log.userid "
                    + "ORDER BY u.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoRecursos = new DTORecursosAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit(); session.close();
            return dtoRecursos;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir una puntuacion por cada recurso sensitivo (visual o multimedia) utilizado
     * @param alumnos lista de alumnos a analizar
     */
    public ArrayList<DTODataMining> getTipoRecursos( ArrayList<String> alumnos) {
        Transaction tx = null;
        Iterator iterador=alumnos.iterator();
        String nombre;
        int diferencia=0;
        ArrayList<DTODataMining> listaResultante = new ArrayList<DTODataMining>();
        try {
            while (iterador.hasNext()){
                nombre=(String)iterador.next();
                Session session = HibernateUtil.getInstance().getSession();
                tx = session.beginTransaction();
                Iterator results = session.createQuery("SELECT r.reference "
                        + "FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u  "
                        + " WHERE modu.name = 'resource'  "
                        + " AND cm.instance=r.id  "
                        + " AND modu.id=cm.module  "
                        + " AND cm.id=log.cmid  "
                        + " AND log.userid=u.id "
                        + " AND u.username='"+nombre+"' "
                        + " AND cmid!=0  "
                        + " GROUP BY r.reference"
                        + " ORDER BY log.time").list().iterator();
                while (results.hasNext()) {
                    // añadimos 1 cada vez que encontramos un recurso multimedia
                    if (this.isMultimedia((String)results.next())){
                        diferencia++;
                    }
                }
                listaResultante.add(new DTOVisitasAlumno(nombre,Integer.toString(diferencia)));
                diferencia=0;
                tx.commit();
                session.close();
            }            
            return listaResultante;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir una lista de cursos de moodle
     */
    public List<DTODataMining> getCourses() {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "select id, shortname from MdlCourse").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                lista.add(new DTORecursosAlumno((String) row[1], Long.toString((Long) row[0])));
            }
            tx.commit(); session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir una puntuacion por cada recurso sensitivo (visual o multimedia) utilizado por curso
     * @param alumnos lista de alumnos a analizar
     * @param curso curso en el que vamos a hacer la consulta
     */
    public ArrayList<DTODataMining> getTipoRecursosPorCurso( ArrayList<String> alumnos,int curso) {
        Transaction tx = null;
        Iterator iterador=alumnos.iterator();
        String nombre;
        int diferencia=0;
        ArrayList<DTODataMining> listaResultante = new ArrayList<DTODataMining>();
        try {
            while (iterador.hasNext()){
                nombre=(String)iterador.next();
                Session session = HibernateUtil.getInstance().getSession();
                tx = session.beginTransaction();
                Iterator results = session.createQuery("SELECT r.reference "
                        + "FROM MdlResource r ,MdlCourseModules cm ,MdlModules modu, MdlLog log , MdlUser u  "
                        + " WHERE modu.name = 'resource'  "
                        + " AND cm.instance=r.id  "
                        + " AND modu.id=cm.module  "
                        + " AND cm.id=log.cmid  "
                        + " AND log.userid=u.id "
                        + " AND u.username='"+nombre+"' "
                        + " AND cmid!=0  "
                        + " AND r.course="+curso+" "
                        + " GROUP BY r.reference"
                        + " ORDER BY log.time").list().iterator();
                while (results.hasNext()) {
                    // añadimos 1 cada vez que encontramos un recurso multimedia
                    if (this.isMultimedia((String)results.next())){
                        diferencia++;
                    }
                }
                listaResultante.add(new DTOVisitasAlumno(nombre,Integer.toString(diferencia)));
                diferencia=0;
                tx.commit();
                session.close();
            }

            return listaResultante;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para buscar patrones de extensiones multimedia dentro de un String
     * @param recurso nombre del recurso
     */
    private boolean isMultimedia(String recurso) {
        if ((recurso.contains(".ppt"))||(recurso.contains(".pptx"))||(recurso.contains(".wmv"))||(recurso.contains(".flv"))
                ||(recurso.contains("youtube.com"))||(recurso.contains(".mpeg"))||(recurso.contains(".jpeg"))||(recurso.contains(".jpg"))
                ||(recurso.contains(".png"))||(recurso.contains(".gif"))
                ||(recurso.contains(".PPT"))||(recurso.contains(".PPTX"))||(recurso.contains(".WMV"))||(recurso.contains(".FLV"))
                ||(recurso.contains("YOUTUBE.COM"))||(recurso.contains(".MPEG"))||(recurso.contains(".JPEG"))||(recurso.contains(".JPG"))
                ||(recurso.contains(".PNG"))||(recurso.contains(".GIF")))
            return true;
        else return false;
    }
}
